1. Identification of Copyright
A portion of the disclosure of this patent document contains material which is subject to copyright protection. The copyright owner has no objection to the facsimile reproduction by anyone of the patent document or the patent disclosure, as it appears in the Patent and Trademark Office patent file or records, but otherwise reserves all copyright rights whatsoever.
2. The Field of the Invention
The present invention relates generally to computer-implemented database systems. More specifically, the present invention relates to a system and method for referencing a user-generated copy of a catalog in a database system.
3. The Relevant Technology
Databases are computerized information storage and retrieval systems. Databases are managed by systems and may take the form of relational databases and hierarchical databases. A Relational Database Management System (RDBMS) is a database system which uses relational techniques for storing and retrieving data. Relational databases are organized into tables consisting of rows (tuples) and columns of data. A relational database typically includes many tables, and each table includes multiple rows and columns. The tables are conventionally stored in direct access storage devices (DASD), such as magnetic or optical disk drives, for semi-permanent storage.
Generally, users communicate with an RDBMS using a Structured Query Language (SQL) interface. The SQL interface allows users to create, manipulate, and query a database by formulating relational operations on the tables, either interactively, in batch files, or embedded in host languages such as C and COBOL. SQL has evolved into a standard language for RDBMS software and has been adopted as such by both the American National Standards Institute (ANSI) and the International Standards Organization (ISO).
The SQL standard provides that each RDBMS should respond to a particular query in the same way, regardless of the underlying database. However, the method that the RDBMS actually uses to find the requested information in the database is left to the RDBMS. Typically, the RDBMS is capable of accessing the requested data in a number of different manners. The RDBMS, therefore, attempts to select the manner that minimizes the computer time and resources (i.e. cost) for executing the query.
When the RDBMS determines how to execute the SQL statements, the set of steps created by the RDBMS for executing the SQL statements is commonly referred to as the xe2x80x9caccess path.xe2x80x9d In other words, the access path is a sequence of operations used by the RDBMS to obtain the data requested by the SQL query. Depending on the access path, an SQL statement might, for instance, search an entire table space, or it might use an index. The access path is a key to determining how well an SQL statement performs. The description of the access path is stored in a table often referred to as a xe2x80x9cplan table,xe2x80x9d which typically stores the access path data for one or more SQL statements.
In addition to determining the access path, many databases estimate the cost (in CPU milliseconds or service units) for executing each SQL statement. Typically, the estimated costs are stored in a table, referred to in the case of DB2(copyright) for OS/390(copyright) as a xe2x80x9cstatement table.xe2x80x9d Like the plan table, the statement table generally stores the estimated statement costs for one or more SQL statements.
Moreover, some databases store information relating to user-defined functions in a table often referred to as a xe2x80x9cfunction table.xe2x80x9d User-defined functions can be very useful in developing database applications. Accordingly, it is advantageous to have information relating to the user-defined functions in a single, convenient location.
Collectively, the above-described access path data, statement cost data, and function data, in addition to object statistic data, are referred to herein as xe2x80x9cexplain data.xe2x80x9d The plan table, statement table, and function table are referred to herein as xe2x80x9cexplain tables.xe2x80x9d As noted above, the explain data is typically generated at bind time. However, the explain data can also be generated dynamically in response to a user-supplied query statement.
Accordingly, many database systems, such as the RDBMS, provide a query explain program which is used to access the explain tables and provide the information within the explain tables to a user for user-selected queries. The query explain programs are in some instances configured to provide the information, referred to herein as query explain data, in a graphical manner, or in a manner otherwise readily comprehensible to a user.
In communicating with the database system, such query explain programs and other database system components must often refer to database organizational information. This information generally includes full description of the database, such as listings and names of tables, columns, and rows within the database. Such information is typically stored in a central location called the xe2x80x9csystem catalog,xe2x80x9d or merely, xe2x80x9cthe catalog.xe2x80x9d
Understandably, the catalog is highly utilized, especially where the database system has many different users, as can be found when organizations such as educational institutions or large corporations utilize the catalog from within many different departments and at many different locations.
Accordingly, when accessing the database system from database system components such as the query explain program it would be beneficial to be able to reference the catalog in a manner that would not increase the burden on the catalog and in a manner wherein the contents of the catalog can be quickly referenced. It would be further beneficial to be able to access the contents in such a manner from remote programs and remote locations across a network.
The present invention solves the foregoing problems by providing a system, method, and article of manufacture for referencing a user-generated copy of a catalog in a database system. In one aspect of the invention, an apparatus for referencing a user-generated copy of a catalog for a query explain program in a database system includes a processor for executing instructions and a memory device having thereon modules of operational data and executable code for execution by the processor.
In one embodiment, the modules include a database system component module configured to refer to a catalog generated by the database system. The modules also preferably include a catalog qualifier designation module communicating with the database system component module and configured to receive a user designation of a user-specified name for a user-generated copy of the catalog generated by the database system to be referenced in place of the catalog generated by the database system.
The database system component module may be a tool for use with the catalog system, such as a query explain program for relaying information regarding the manner of execution of selected queries to a user. In one embodiment, the database system is adapted for operation on a server and the database system component module is adapted for operation on a workstation communicating with the database system, and wherein the catalog and the user-generated copy of the catalog are stored on the server.
The catalog qualifier designation module may comprise a graphical user interface (GUI) window accessible from the database system component module. The GUI window preferably comprises a control mechanism for receiving the user designation of a user-specified name in place of the original name of the catalog generated by the database system.
The modules may also comprise a catalog referencing module. Preferably, the catalog referencing module is adapted to communicate with the catalog qualifier designation module, to receive the user-specified name for a user-generated copy of the catalog, and to consult the user-generated copy of the catalog for use by the database system component module. The catalog referencing module may also be adapted to reference the catalog generated by the database system in lieu of receiving the user-specified name for a user-generated copy of the catalog.
Preferably, the user-specified name comprises a high level qualifier, and may designate the maker of the user-generated copy of the catalog. In one embodiment, the database system component module references the user-designated catalog integrally as if the user-designated catalog were the catalog generated by the database system.
In another aspect of the invention, a method for referencing a user-generated copy of a catalog in a database system is provided and includes a step of receiving a user designation of a user-specified name for a user-generated copy of a catalog generated by the database system. The method also preferably comprises a step of referencing the user-generated copy in place of the catalog generated by the database system, using the designated user-specified name. The method may also comprise the step of creating a user-generated copy of the catalog created by the database system and giving the user-generated copy of the catalog a user-specified name.
In one embodiment, the step of receiving a user designation of a user-specified name comprises receiving a user designation of a user-specified name within a database system component module. Additionally, the database system component module may comprise an explain program for relaying information regarding query execution to a user.
The method may also comprise generating a graphical user interface window accessible from the database system component module. Preferably, the window comprises a control mechanism for receiving the user designation of a user-specified name in place of the original name of the catalog generated by the database system.
Thus, users may reference the catalog generated by the database system in lieu of receiving the user-specified name for a user-generated copy of the catalog. Preferably, the user-specified name comprises a high level qualifier, which may designate the maker of the user-generated copy of the catalog. Additionally, referencing the user-generated copy may comprise referencing the user-designated catalog integrally as if the user-designated catalog were the catalog generated by the database system.
In yet another aspect of the invention, an article of manufacture comprises a program storage medium readable by a processor and embodying one or more instructions executable by the processor to perform the above-described method for referencing a user-generated copy of a catalog in a database system.
These and other objects, features, and advantages of the present invention will become more fully apparent from the following description and appended claims, or may be learned by the practice of the invention as set forth in the following specification.